
CREATE TABLE User
(
    idUsr INTEGER NOT NULL,
    loginUsr VARCHAR(128) NOT NULL,
    mailUsr VARCHAR(128) NOT NULL,
    pwdUser VARCHAR(32) NOT NULL,
    lastCon date,
    UNIQUE(idUsr),
    UNIQUE(mailUsr),
    PRIMARY KEY(idUsr)
);

BEGIN;
INSERT INTO User VALUES(NULL,'Romain', 'lapray.romain@gmail.com', 'd28e12b5a5b6bd69ce91065f09b7c037', NULL); /*!encule666*/
INSERT INTO User VALUES(NULL,'Katia', 'katia.rencurel@gmail.com', 'd28e12b5a5b6bd69ce91065f09b7c037', NULL);
COMMIT;

/*
SELECT      loginUsr as login, 
            lastCon as 'Last Connection' 
FROM        User 
ORDER BY    idUsr;
*/

CREATE TABLE Category
(
    idCat INTEGER NOT NULL,
    nameCat VARCHAR(128) NOT NULL,
    descCat text,
    iconCat VARCHAR(512),
    UNIQUE(idCat),
    UNIQUE(nameCat),
    PRIMARY KEY(idCat)
);

BEGIN;
INSERT INTO Category VALUES(1, 'Assurance', '', 'img/icons/assurance.png');
INSERT INTO Category VALUES(2, 'Factures et Garanties', '', 'img/icons/factgarant.png');
INSERT INTO Category VALUES(3, 'Famille', '', 'img/icons/famille.png');
INSERT INTO Category VALUES(4, 'Finances', '', 'img/icons/finance.png');
INSERT INTO Category VALUES(5, 'Logement (location)', '', 'img/icons/logement.png');
INSERT INTO Category VALUES(6, 'Logement (propriétaire)', '', 'img/icons/logement.png');
INSERT INTO Category VALUES(7, 'Santé', '', 'img/icons/sante.png');
INSERT INTO Category VALUES(8, 'Vie Professionelle (salarié)', '', 'img/icons/viepro.png');
INSERT INTO Category VALUES(9, 'Vie Professionelle (employeur)', '', 'img/icons/viepro.png');
INSERT INTO Category VALUES(10, 'Véhicules', '', 'img/icons/vehicule.png');
INSERT INTO Category VALUES(11, 'Autres', '', 'img/icons/autres.png');
COMMIT;

/*
SELECT      nameCat as name, 
            descCat as desc, 
            iconCat as icon 
FROM        Category 
ORDER BY    idCat;
*/

CREATE TABLE SubCategory
(
    idSCat INTEGER NOT NULL,
    nameSCat VARCHAR(128),
    descSCat text,
    iconSCat VARCHAR(512),
    categoryIdSCat INTEGER NOT NULL,
    UNIQUE(idSCat),
    UNIQUE(nameSCat, categoryIdSCat),
    PRIMARY KEY(idSCat)
);

BEGIN;
INSERT INTO SubCategory VALUES(1, 'Contrats et Attestations', '', 'img/icons/error.png', 1);
INSERT INTO SubCategory VALUES(2, 'Courriers', '', 'img/icons/error.png', 1);
INSERT INTO SubCategory VALUES(3, 'Preuves de valeurs', '', 'img/icons/error.png', 1);
INSERT INTO SubCategory VALUES(4, 'Sinistres', '', 'img/icons/error.png', 1);

INSERT INTO SubCategory VALUES(5, 'Achats exceptionnels', '', 'img/icons/error.png', 2);
INSERT INTO SubCategory VALUES(6, 'Travaux', '', 'img/icons/error.png', 2);
INSERT INTO SubCategory VALUES(7, 'Equipements de la maison', '', 'img/icons/error.png', 2);
INSERT INTO SubCategory VALUES(8, 'Prestations de service', '', 'img/icons/error.png', 2);
INSERT INTO SubCategory VALUES(9, 'Vêtements', '', 'img/icons/error.png', 2);

INSERT INTO SubCategory VALUES(10, 'Contrats et actes', '', 'img/icons/error.png', 3);
INSERT INTO SubCategory VALUES(11, 'Formation / Scolarité', '', 'img/icons/error.png', 3);
INSERT INTO SubCategory VALUES(12, 'Loisirs', '', 'img/icons/error.png', 3);
INSERT INTO SubCategory VALUES(13, 'Papiers d''identité', '', 'img/icons/error.png', 3);

INSERT INTO SubCategory VALUES(14, 'Banques', '', 'img/icons/error.png', 4);
INSERT INTO SubCategory VALUES(15, 'Impôts', '', 'img/icons/error.png', 4);

INSERT INTO SubCategory VALUES(16, 'Contrats', '', 'img/icons/error.png', 5);
INSERT INTO SubCategory VALUES(17, 'Courriers', '', 'img/icons/error.png', 5);
INSERT INTO SubCategory VALUES(18, 'Quittances', '', 'img/icons/error.png', 5);
INSERT INTO SubCategory VALUES(19, 'Travaux et entretien', '', 'img/icons/error.png', 5);

INSERT INTO SubCategory VALUES(20, 'Contrats et titres', '', 'img/icons/error.png', 6);
INSERT INTO SubCategory VALUES(21, 'Copropriété', '', 'img/icons/error.png', 6);
INSERT INTO SubCategory VALUES(22, 'Travaux et entretien', '', 'img/icons/error.png', 6);

INSERT INTO SubCategory VALUES(23, 'Dossiers médicaux', '', 'img/icons/error.png', 7);
INSERT INTO SubCategory VALUES(24, 'Sécu / Mutuelle', '', 'img/icons/error.png', 7);

INSERT INTO SubCategory VALUES(25, 'Contrats et courriers', '', 'img/icons/error.png', 8);
INSERT INTO SubCategory VALUES(26, 'Préparation retraite', '', 'img/icons/error.png', 8);

INSERT INTO SubCategory VALUES(27, 'Employeur', '', 'img/icons/error.png', 9);

INSERT INTO SubCategory VALUES(28, 'Véhicules', 'Voiture, Moto, Bateaux...', 'img/icons/error.png', 10);

INSERT INTO SubCategory VALUES(29, 'Hors categorie', 'Documents inclassables autrepart.','img/icons/error.png', 12);
COMMIT;
/*
SELECT      c.nameCat as sname, 
            c.descCat as sdesc, 
            c.iconCat as sicon,
            sc.nameSCat as scname,
            sc.descSCat as scdesc,
            sc.iconSCat as scicon
FROM        Category c, SubCategory sc
WHERE       sc.CategoryIdSCat = c.idCat  
ORDER BY    c.idCat, sc.idSCat;
*/

CREATE TABLE Type
(
    idType INTEGER NOT NULL,
    nameType VARCHAR(128) NOT NULL,
    descType text,
    UNIQUE(idType),
    UNIQUE(nameType),
    PRIMARY KEY(idType)
);

BEGIN;
INSERT INTO Type VALUES(1, 'Contrat d''assurance habitation (avec responsabilité civile)', '');
INSERT INTO Type VALUES(2, 'Contrat d''assurance habitation (sans responsabilité civile)', '');
INSERT INTO Type VALUES(3, 'Contrat d''assurance auto', '');
INSERT INTO Type VALUES(4, 'Contrat d''assurance moto', '');
INSERT INTO Type VALUES(5, 'Contrat d''assurance bateau', '');
INSERT INTO Type VALUES(6, 'Contrat d''assurance décès', '');
INSERT INTO Type VALUES(7, 'Contrat d''assurance sur la vie', '');
INSERT INTO Type VALUES(8, 'Contrat auprès des pompes funèbres', '');
INSERT INTO Type VALUES(9, 'Contrat d''assurance professionnelle', '');

INSERT INTO Type VALUES(10, 'Double d''une lettre de résiliation', '');
INSERT INTO Type VALUES(11, 'Accusé de réception', '');

INSERT INTO Type VALUES(12, 'Photos des meubles', '');
INSERT INTO Type VALUES(13, 'Oeuvres d''arts', '');
INSERT INTO Type VALUES(14, 'Objets de valeur', 'hifi, cd, dvd...');
INSERT INTO Type VALUES(15, 'Certificat d''authenticité', 'Bijoux, meubles, oeuvres d''art...');
INSERT INTO Type VALUES(16, 'Estimation d''experts', '');

INSERT INTO Type VALUES(17, 'Dossier de règlement des sinistres par l''assureur', '');
INSERT INTO Type VALUES(18, 'Dossier d''accident corporel - factures', '');
INSERT INTO Type VALUES(19, 'Dossier d''accident corporel - expertises', '');
INSERT INTO Type VALUES(20, 'Dossier d''accident corporel - certificats médicaux', '');
INSERT INTO Type VALUES(21, 'Dossier d''accident corporel - autres', '');

INSERT INTO Type VALUES(22, 'Factures', '');
INSERT INTO Type VALUES(23, 'Garanties', '');

INSERT INTO Type VALUES(24, 'Contrats de travaux', '');
INSERT INTO Type VALUES(25, 'Contrats d''architecte', '');
INSERT INTO Type VALUES(26, 'Factures et preuves de paiement des travaux', '');
INSERT INTO Type VALUES(27, 'Procès verbaux de réception des travaux', '');

INSERT INTO Type VALUES(28, 'Mobilier', '');
INSERT INTO Type VALUES(29, 'Electroménager', '');
INSERT INTO Type VALUES(30, 'HI-FI', '');
INSERT INTO Type VALUES(31, 'Informatique', '');
INSERT INTO Type VALUES(32, 'Bricolage', '');
INSERT INTO Type VALUES(33, 'Jardinage', '');
INSERT INTO Type VALUES(34, 'Autres équipements', '');

INSERT INTO Type VALUES(35, 'Artisans', '');
INSERT INTO Type VALUES(36, 'Eau', '');
INSERT INTO Type VALUES(37, 'Electricité', '');
INSERT INTO Type VALUES(38, 'Téléphone', '');
INSERT INTO Type VALUES(39, 'Hotel et Restaurant', '');

INSERT INTO Type VALUES(40, 'Costumes', '');
INSERT INTO Type VALUES(41, 'Robes de soirée', '');

INSERT INTO Type VALUES(42, 'Acte d''état civil - copies intégrales', '');
INSERT INTO Type VALUES(43, 'Acte d''état civil - extraits', '');
INSERT INTO Type VALUES(44, 'Contrat de mariage', '');
INSERT INTO Type VALUES(45, 'PACS', '');
INSERT INTO Type VALUES(46, 'Jugement de divorce', '');
INSERT INTO Type VALUES(47, 'Jugement de séparation', '');
INSERT INTO Type VALUES(48, 'Union libre', '');
INSERT INTO Type VALUES(49, 'Décision de justice', '');
INSERT INTO Type VALUES(50, 'Tutelles', '');
INSERT INTO Type VALUES(51, 'Curatelles', '');
INSERT INTO Type VALUES(52, 'Successions', '');
INSERT INTO Type VALUES(53, 'Actes de donation', '');
INSERT INTO Type VALUES(54, 'Dispositions testamentaire', '');
INSERT INTO Type VALUES(55, 'Actes de reconnaissance d''un enfant', '');
INSERT INTO Type VALUES(56, 'Actes d''adoption', '');

INSERT INTO Type VALUES(NULL, 'Diplomes universitaires', '');
INSERT INTO Type VALUES(NULL, 'Diplomes professionnels', '');
INSERT INTO Type VALUES(NULL, 'Dossiers scolaires', '');

INSERT INTO Type VALUES(NULL, 'Carte de membre de club', '');
INSERT INTO Type VALUES(NULL, 'Association', '');
INSERT INTO Type VALUES(NULL, 'Parti politique', '');
INSERT INTO Type VALUES(NULL, 'Licences sportives', '');
INSERT INTO Type VALUES(NULL, 'Licences professionnelles', '');
INSERT INTO Type VALUES(NULL, 'Licences associatives', '');
INSERT INTO Type VALUES(NULL, 'Carte d''abonnement', '');
INSERT INTO Type VALUES(NULL, 'Carte de réduction', '');

INSERT INTO Type VALUES(NULL, 'Carte d''identité', '');
INSERT INTO Type VALUES(NULL, 'Passeport', '');
INSERT INTO Type VALUES(NULL, 'Permis de conduire', '');
INSERT INTO Type VALUES(NULL, 'Livret de famille', '');
INSERT INTO Type VALUES(NULL, 'Visas', '');
INSERT INTO Type VALUES(NULL, 'Carte de séjour', '');
INSERT INTO Type VALUES(NULL, 'Carte de résident', '');
INSERT INTO Type VALUES(NULL, 'Carte d''étudiant', '');

INSERT INTO Type VALUES(NULL, 'Contrats des banques', '');
INSERT INTO Type VALUES(NULL, 'RIB', '');
INSERT INTO Type VALUES(NULL, 'RIP', '');
INSERT INTO Type VALUES(NULL, 'Facturettes de carte bancaire', '');
INSERT INTO Type VALUES(NULL, 'Relevés de comptes', '');
INSERT INTO Type VALUES(NULL, 'Virements', '');
INSERT INTO Type VALUES(NULL, 'Prélèvements', '');
INSERT INTO Type VALUES(NULL, 'Prêt imobilier', '');
INSERT INTO Type VALUES(NULL, 'Prêt à la consomation', '');

INSERT INTO Type VALUES(NULL, 'Avis d''imposition', '');
INSERT INTO Type VALUES(NULL, 'Déclaration de revenu', '');
INSERT INTO Type VALUES(NULL, 'Justificatifs de réduction/déduction', '');
INSERT INTO Type VALUES(NULL, 'Déclaration d''impot sur la fortune', '');
INSERT INTO Type VALUES(NULL, 'Avis de paiement de l''impot sur la fortune', '');
INSERT INTO Type VALUES(NULL, 'Impots locaux', '');
INSERT INTO Type VALUES(NULL, 'Attestation de paiement', '');

COMMIT;